package com.maybach.core.ao.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.alibaba.fastjson.JSON;
import com.audi.dubbo.api.common.enums.CarFuelEnum;
import com.audi.dubbo.api.common.enums.CarStandardEnum;
import com.audi.dubbo.api.common.enums.CarTypeEnum;
import com.audi.dubbo.api.item.request.B2BCarRequest;
import com.audi.dubbo.api.item.response.impl.InduceErrorReq;
import com.maybach.common.enums.PartnerStatusEnum;
import com.maybach.common.model.ImportModuleDO;
import com.maybach.common.model.PartnerDO;
import com.maybach.common.model.UserDO;
import com.maybach.common.query.PartnerQuery;
import com.maybach.common.query.UserQuery;
import com.maybach.core.ao.ImportCarAO;
import com.maybach.core.form.product.ModuleForm;
import com.maybach.core.manager.ImageManager;
import com.maybach.core.manager.PartnerManager;
import com.maybach.core.manager.UserManager;
import com.maybach.core.manager.impl.ImageManagerImpl;
import com.maybach.util.CalendarUtil;
import com.maybach.util.MaybachUtil;
import com.maybach.util.PriceUtil;
import com.maybach.util.StringUtil;
import com.maybach.util.sms.support.RegexUtil;
import com.qiniu.api.io.IoApi;
import com.qiniu.api.io.PutExtra;
import com.qiniu.api.io.PutRet;

import lombok.extern.slf4j.Slf4j;

/**
 * Created by pyshift on 16/3/14.
 */
@Component
@Slf4j
public class ImportCarAOImpl implements ImportCarAO {

	@Autowired
	private UserManager userManager;
	@Autowired
	private ImageManager imageManager;
	@Autowired
	private PartnerManager partnerManager;

	private List<String> COL_HEADERS = Arrays.asList("公司/联系人/电话", "车型", "燃油", "车规", "外观/内饰", "车架号", "配置", "价格", "车辆状态",
			"备注");
	private List<Integer> COL_REQUIRED = Arrays.asList(1, 1, 0, 0, 1, 0, 0, 0, 0, 0);

	private List<String> COL_CHINA_HEADERS = Arrays.asList("序号", "公司", "联系人", "电话", "车型", "指导价", "价格", "燃油", "外观", "内饰",
			"车辆状态", "备注");
	private List<Integer> COL_CHINA_REQUIRED = Arrays.asList(0, 0, 0, 1, 1, 1, 0, 0, 1, 0, 0, 0);
	private static final String GUIDE_PRICE_UNIT = "万";

	@Override
	public List<B2BCarRequest> excelDataCheckByParallel(Row row, String sheetName, int rowIndex, Set<String> errorList,
			int defaultType, int defaultFuel, int defaultSpec) {
		Set<String> set = new HashSet<String>();
		boolean suc = false;
		B2BCarRequest rowWrapper = new B2BCarRequest();
		rowWrapper.setByTemplate(0);
		List<String> facadeList = new ArrayList<String>();
		for (int i = 0; i < COL_REQUIRED.size(); i++) {
			String stringValue = getStringValue(row, i);
			if (StringUtil.isBlank(stringValue)) {
				if (COL_REQUIRED.get(i) == 1) {
					logCellFormatError(sheetName, rowIndex, i, set, COL_HEADERS);
				}
				continue;
			}
			if (i == 0) {// 公司/联系人/电话 格式回车分割，上面是公司，下面是姓名+电话
				String regEx = "[^0-9]";
				Pattern p = Pattern.compile(regEx);
				Matcher m = p.matcher(getMobileNo(row, i));
				String mobile = m.replaceAll("").trim();
				String[] mobileSeg = stringValue.split("-");
				int segLen = mobileSeg.length;
				String suffixMobile = mobileSeg[segLen - 1];
				if (!RegexUtil.match(mobile, RegexUtil.PHONE_REGEX) && !suffixMobile.matches("^\\d{4,5}$")) {
					errorList.add(sheetName + "工作表格中存在非正确的号码:" + mobile);
					continue;
				}
				// 用手机号找sellerID
				Long sellerId;
				if (RegexUtil.match(mobile, RegexUtil.PHONE_REGEX)) {
					sellerId = getSellerId(mobile);
				} else {
					sellerId = getSellerId(suffixMobile);
				}
				if (sellerId != null && sellerId != -1L) {
					rowWrapper.setSellerId(sellerId);
				} else {
					errorList.add(sheetName + "工作表格中存在此电话找不到供应商:" + mobile);
				}
				rowWrapper.setSeriesStr(stringValue);// 暂存经销商名称
			} else {
				switch (i) {
				case 1:// 型号
					rowWrapper.setModelStr(stringValue);
					suc = true;
					break;
				case 2:// 燃油
					rowWrapper.setFuelStr(stringValue);
					break;
				case 3:// 车规
					rowWrapper.setSpecStr(stringValue);
					break;
				case 4:// 外观--内饰
					if (stringValue.contains("/")) {
						String[] arr = stringValue.split("/");
						facadeList.add(arr[0]);
						if (arr.length == 2) {
							rowWrapper.setTrimStr(arr[1]);
						}
					} else {
						if (stringValue.length() > 1) {
							stringValue = stringValue.replace("色", "");
							facadeList.add(stringValue.substring(0, 1));
							String trimStr = stringValue.substring(1);
							if (StringUtil.isNotBlank(trimStr)) {
								rowWrapper.setTrimStr(trimStr);
							}
						} else {
							rowWrapper.setFacadeStr(stringValue);
						}
					}
					break;
				case 5:// 车架号
					rowWrapper.setFrameNo(stringValue.replace(".0", ""));
					break;
				case 6:// 配置
					rowWrapper.setConfigure(stringValue);
					break;
				case 7:// 价格
					rowWrapper.setPriceStr(getStringValue(row, i));
					Long price = getPrice(row, i);
					rowWrapper.setPrice(price);
					break;
				case 8:// 车辆状态
					rowWrapper.setTypeStr(stringValue);
					break;
				case 9:// 备注
					rowWrapper.setRemark(stringValue);
					break;
				}
			}
		}
		if (suc) {
			errorList.addAll(set);
			return b2BCarRequestsListBuilder(rowWrapper, facadeList, rowIndex, defaultFuel, defaultSpec, defaultType);
		} else {
			return null;
		}
	}

	@Override
	public List<B2BCarRequest> excelDataCheckByChina(Row row, String sheetName, int rowIndex, Set<String> errorList,
			int defaultType, int defaultFuel) {
		Set<String> set = new HashSet<String>();
		boolean suc = false;
		B2BCarRequest rowWrapper = new B2BCarRequest();
		rowWrapper.setByTemplate(0);
		List<String> facadeList = new ArrayList<String>();
		for (int i = 3; i < COL_CHINA_REQUIRED.size(); i++) {
			String stringValue = getStringValue(row, i);
			if (StringUtil.isBlank(stringValue)) {
				if (COL_CHINA_REQUIRED.get(i) == 1) {
					logCellFormatError(sheetName, rowIndex, i, set, COL_CHINA_HEADERS);
				}
				if (i != 6) {
					continue;
				}
			}
			if (i == 3) {// 公司/联系人/电话 格式回车分割，上面是公司，下面是姓名+电话
				String regEx = "[^0-9]";
				Pattern p = Pattern.compile(regEx);
				stringValue = getMobileNo(row, i);
				Matcher m = p.matcher(stringValue);
				String mobile = m.replaceAll("").trim();
				String[] mobileSeg = stringValue.split("-");
				int segLen = mobileSeg.length;
				String suffixMobile = mobileSeg[segLen - 1];
				if (!RegexUtil.match(mobile, RegexUtil.PHONE_REGEX) && !suffixMobile.matches("^\\d{4,5}$")) {
					errorList.add(sheetName + "工作表格中存在非正确的号码:" + mobile);
					continue;
				}
				// 用手机号找sellerID
				Long sellerId;
				if (RegexUtil.match(mobile, RegexUtil.PHONE_REGEX)) {
					sellerId = getSellerId(mobile);
				} else {
					sellerId = getSellerId(suffixMobile);
				}
				if (sellerId != null && sellerId != -1L) {
					rowWrapper.setSellerId(sellerId);
				} else {
					errorList.add(sheetName + "工作表格中存在此电话找不到供应商:" + mobile);
				}
				rowWrapper.setSeriesStr(stringValue);// 暂存经销商名称
			} else {
				switch (i) {
				case 4:// 型号
					rowWrapper.setModelStr(stringValue);
					suc = true;
					break;
				case 5:// 指导价
					if (stringValue == null || !RegexUtil.match(stringValue, RegexUtil.NUMBER_POINT)) {
						logCellFormatError(sheetName, rowIndex, i, set, COL_CHINA_HEADERS);
						break;
					}
					Double guiPrice = Double.parseDouble(stringValue);
					if (guiPrice < 10000) {
						rowWrapper.setGuidePrice(stringValue);
					} else {
						rowWrapper
								.setGuidePrice(PriceUtil.formatWYuanWithPreTen(new Double(guiPrice * 100).longValue()));// 转换为万元
					}
					break;
				case 6:// 价格
					rowWrapper.setPriceStr(getStringValue(row, i));
					Long price = getChinaPrice(row, i, rowWrapper.getGuidePrice());
					rowWrapper.setPrice(price);
					break;
				case 7:// 燃油
					rowWrapper.setFuelStr(stringValue);
					break;
				case 8:// 外观
					facadeList.add(stringValue);
					break;
				case 9:// 内饰
					rowWrapper.setTrimStr(stringValue);
					break;
				case 10:// 车辆状态
					rowWrapper.setTypeStr(stringValue);
					break;
				case 11:// 备注
					rowWrapper.setRemark(stringValue);
					break;
				}
			}
		}
		if (suc) {
			errorList.addAll(set);
			String guidePriceStr = rowWrapper.getGuidePrice() + GUIDE_PRICE_UNIT;
			rowWrapper.setGuidePrice(guidePriceStr);
			return b2BCarRequestsListBuilder(rowWrapper, facadeList, rowIndex, defaultFuel, 1, defaultType);
		} else {
			return null;
		}
	}

	@Override
	public List<B2BCarRequest> textDataCheckByChina(Row row, String sheetName, int rowIndex, Set<String> errorList,
			int defaultType, int defaultFuel) {
		return null;
	}

	/**
	 * 写入EXCEL
	 *
	 * @param requests
	 * @return
	 */
	@Override
	public String writeExcelByExcelParallel(List<InduceErrorReq> requests) {
		if (CollectionUtils.isEmpty(requests)) {
			return null;
		}
		Workbook wb = new XSSFWorkbook();// 创建新Workbook对象
		setExcelStyle(wb);// 执行样式初始化
		Sheet sheet = wb.createSheet(CalendarUtil.formatDate(new Date(), CalendarUtil.DATE_FMT_6) + "导入错误信息");// 创建新的sheet对象
		Row sheetRow = sheet.createRow((short) 0);

		for (int i = 0; i < COL_HEADERS.size(); i++) {
			sheetRow.createCell(i).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(i).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(i).setCellValue(COL_HEADERS.get(i));
		}
		for (int i = 0; i < requests.size(); i++) {// 行
			InduceErrorReq a = requests.get(i);
			if (a == null) {
				continue;
			}
			B2BCarRequest b2BCarRequest = a.getErrorReq();
			if (b2BCarRequest == null) {
				continue;
			}
			Row row = sheet.createRow((short) i + 4);
			row.setHeightInPoints(20);// 20像素
			for (int j = 0; j < COL_HEADERS.size(); j++) {
				Cell cell = row.createCell(j);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				switch (j) {
				case 0:// 公司联系人
					cell.setCellValue(b2BCarRequest.getSeriesStr());
					break;
				case 1:// 型号
					cell.setCellValue(b2BCarRequest.getModelStr());
					break;
				case 2:// 燃油
					cell.setCellValue(b2BCarRequest.getFuelStr());
					break;
				case 3:// 车规
					cell.setCellValue(b2BCarRequest.getSpecStr());
					break;
				case 4:// 外观--内饰
					StringBuilder sb = new StringBuilder();
					sb.append(b2BCarRequest.getFacadeStr());
					if (StringUtil.isNotBlank(b2BCarRequest.getTrimStr())) {
						sb.append(b2BCarRequest.getTrimStr());
					}
					cell.setCellValue(sb.toString());
					break;
				case 5:// 车架号
					cell.setCellValue(b2BCarRequest.getFrameNo());
					break;
				case 6:// 配置
					cell.setCellValue(b2BCarRequest.getConfigure());
					break;
				case 7:// 价格
					if (b2BCarRequest.getPrice() == null || b2BCarRequest.getPrice() <= 0) {
						cell.setCellValue("电议");
					} else {
						cell.setCellValue(PriceUtil.formatCNYW(b2BCarRequest.getPrice()));
					}
					break;
				case 8:// 车辆状态
					cell.setCellValue(b2BCarRequest.getTypeStr());
					break;
				case 9:// 备注
					cell.setCellValue(b2BCarRequest.getRemark());
					break;
				}
			}

			Cell cell = row.createCell(COL_HEADERS.size());
			cell.setCellStyle(titleStyle);// 设置标题样式
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(a.getErrorMess());
		}
		return updateExcel(wb);
	}

	@Override
	public String writeExcelByExcelChina(List<InduceErrorReq> requests) {
		if (CollectionUtils.isEmpty(requests)) {
			return null;
		}
		Workbook wb = new XSSFWorkbook();// 创建新Workbook对象
		setExcelStyle(wb);// 执行样式初始化
		Sheet sheet = wb.createSheet(CalendarUtil.formatDate(new Date(), CalendarUtil.DATE_FMT_6) + "导入错误信息");// 创建新的sheet对象
		Row sheetRow = sheet.createRow((short) 0);

		for (int i = 0; i < COL_CHINA_HEADERS.size(); i++) {
			sheetRow.createCell(i).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(i).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(i).setCellValue(COL_CHINA_HEADERS.get(i));
		}
		for (int i = 0; i < requests.size(); i++) {// 行
			InduceErrorReq a = requests.get(i);
			if (a == null) {
				continue;
			}
			B2BCarRequest b2BCarRequest = a.getErrorReq();
			if (b2BCarRequest == null) {
				continue;
			}
			Row row = sheet.createRow((short) i + 4);
			row.setHeightInPoints(20);// 20像素
			for (int j = 3; j < COL_CHINA_HEADERS.size(); j++) {
				Cell cell = row.createCell(j);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				switch (j) {
				case 3:// 公司联系人
					cell.setCellValue(b2BCarRequest.getSeriesStr());
					break;
				case 4:// 型号
					cell.setCellValue(b2BCarRequest.getModelStr());
					break;
				case 5:// 指导价
					cell.setCellValue(b2BCarRequest.getGuidePrice());
					break;
				case 6:// 价格
					cell.setCellValue(b2BCarRequest.getPriceStr());
					break;
				case 7:// 燃油
					cell.setCellValue(b2BCarRequest.getFuelStr());
					break;
				case 8:// 外观
					cell.setCellValue(b2BCarRequest.getFacadeStr());
					break;
				case 9:// 内饰
					cell.setCellValue(b2BCarRequest.getTrimStr());
					break;
				case 10:// 车辆状态
					cell.setCellValue(b2BCarRequest.getTypeStr());
					break;
				case 11:// 备注
					cell.setCellValue(b2BCarRequest.getRemark());
					break;
				}
			}

			Cell cell = row.createCell(COL_CHINA_HEADERS.size());
			cell.setCellStyle(titleStyle);// 设置标题样式
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(a.getErrorMess());
		}
		return updateExcel(wb);
	}

	@Override
	public String writeExcelByExcelModule(ImportModuleDO importModuleDO, List<InduceErrorReq> requests) {
		if (CollectionUtils.isEmpty(requests)) {
			return null;
		}

		int maxIndex = 0;
		ModuleForm moduleForm = JSON.parseObject(importModuleDO.getModule(), ModuleForm.class);
		Workbook wb = new XSSFWorkbook();// 创建新Workbook对象
		setExcelStyle(wb);// 执行样式初始化
		Sheet sheet = wb.createSheet(
				CalendarUtil.formatDate(new Date(), CalendarUtil.DATE_FMT_6) + importModuleDO.getFeature() + "导入错误信息");// 创建新的sheet对象
		setModuleRow(sheet, moduleForm);

		for (int rowIndex = 0; rowIndex < requests.size(); rowIndex++) {// 行
			InduceErrorReq induceErrorReq = requests.get(rowIndex);
			if (induceErrorReq == null) {
				continue;
			}
			B2BCarRequest b2BCarRequest = induceErrorReq.getErrorReq();
			if (b2BCarRequest == null) {
				continue;
			}
			Row row = sheet.createRow((short) rowIndex + 1);
			row.setHeightInPoints(20);// 20像素

			// 车型名称
			for (Integer i : MaybachUtil.getInteger(moduleForm.getModel())) {
				maxIndex = maxIndex > i ? maxIndex : i;
				Cell cell = row.createCell(i - 1);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(b2BCarRequest.getModelStr());
			}

			// 燃油
			for (Integer i : MaybachUtil.getInteger(moduleForm.getFuel())) {
				maxIndex = maxIndex > i ? maxIndex : i;
				Cell cell = row.createCell(i - 1);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(b2BCarRequest.getFuelStr());
			}

			// 车规
			for (Integer i : MaybachUtil.getInteger(moduleForm.getStandard())) {
				maxIndex = maxIndex > i ? maxIndex : i;
				Cell cell = row.createCell(i - 1);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(b2BCarRequest.getSpecStr());
			}

			int facadeIndex = Integer.parseInt(moduleForm.getFacade()) - 1;
			int trimIndex = Integer.parseInt(moduleForm.getTrim()) - 1;
			if (facadeIndex == trimIndex) {
				maxIndex = maxIndex > facadeIndex + 1 ? maxIndex : facadeIndex + 1;
				Cell cell = row.createCell(facadeIndex - 1);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(b2BCarRequest.getFacadeStr() + "/" + b2BCarRequest.getTrimStr());
			} else {

				Cell facade = row.createCell(facadeIndex - 1);
				facade.setCellStyle(titleStyle);// 设置标题样式
				facade.setCellType(HSSFCell.CELL_TYPE_STRING);
				facade.setCellValue(b2BCarRequest.getFacadeStr());

				Cell trim = row.createCell(trimIndex - 1);
				trim.setCellStyle(titleStyle);// 设置标题样式
				trim.setCellType(HSSFCell.CELL_TYPE_STRING);
				trim.setCellValue(b2BCarRequest.getTrimStr());
			}

			// 车架号
			for (Integer i : MaybachUtil.getInteger(moduleForm.getFrameNo())) {
				maxIndex = maxIndex > i ? maxIndex : i;
				Cell cell = row.createCell(i - 1);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(b2BCarRequest.getFrameNo());
			}

			// 配置
			for (Integer i : MaybachUtil.getInteger(moduleForm.getConfigure())) {
				maxIndex = maxIndex > i ? maxIndex : i;
				Cell cell = row.createCell(i - 1);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(b2BCarRequest.getConfigure());
			}

			// 价格
			int column = Integer.parseInt(moduleForm.getPrice()) - 1;
			maxIndex = maxIndex > column + 1 ? maxIndex : column + 1;
			Cell price = row.createCell(column);
			price.setCellStyle(titleStyle);// 设置标题样式
			price.setCellType(HSSFCell.CELL_TYPE_STRING);
			price.setCellValue(b2BCarRequest.getPriceStr());

			// 车辆状态
			for (Integer i : MaybachUtil.getInteger(moduleForm.getType())) {
				maxIndex = maxIndex > i ? maxIndex : i;
				Cell cell = row.createCell(i - 1);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(b2BCarRequest.getTypeStr());
			}
			// 备注
			for (Integer i : MaybachUtil.getInteger(moduleForm.getRemark())) {
				maxIndex = maxIndex > i ? maxIndex : i;
				Cell cell = row.createCell(i - 1);
				cell.setCellStyle(titleStyle);// 设置标题样式
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(b2BCarRequest.getRemark());
			}

			Cell cell = row.createCell(maxIndex);
			cell.setCellStyle(titleStyle);// 设置标题样式
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(induceErrorReq.getErrorMess());
		}
		return updateExcel(wb);
	}

	private void setModuleRow(Sheet sheet, ModuleForm moduleForm) {
		Row sheetRow = sheet.createRow((short) 0);

		// 车型名称
		for (Integer i : MaybachUtil.getInteger(moduleForm.getModel())) {
			String stringValue = getStringValue(sheetRow, i - 1);
			sheetRow.createCell(i - 1).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(i - 1).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(i - 1).setCellValue(stringValue + " 车型名称");
		}

		// 燃油
		for (Integer i : MaybachUtil.getInteger(moduleForm.getFuel())) {
			String stringValue = getStringValue(sheetRow, i - 1);
			sheetRow.createCell(i - 1).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(i - 1).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(i - 1).setCellValue(stringValue + " 燃油");
		}

		// 车规
		for (Integer i : MaybachUtil.getInteger(moduleForm.getStandard())) {
			String stringValue = getStringValue(sheetRow, i - 1);
			sheetRow.createCell(i - 1).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(i - 1).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(i - 1).setCellValue(stringValue + " 车规");
		}

		int facadeIndex = Integer.parseInt(moduleForm.getFacade()) - 1;
		int trimIndex = Integer.parseInt(moduleForm.getTrim()) - 1;
		if (facadeIndex == trimIndex) {
			sheetRow.createCell(facadeIndex).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(facadeIndex).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(facadeIndex).setCellValue("外观/内饰");
		} else {

			sheetRow.createCell(facadeIndex).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(facadeIndex).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(facadeIndex).setCellValue("外观");

			sheetRow.createCell(trimIndex).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(trimIndex).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(trimIndex).setCellValue("内饰");
		}

		// 车架号
		for (Integer i : MaybachUtil.getInteger(moduleForm.getFrameNo())) {
			String stringValue = getStringValue(sheetRow, i - 1);
			sheetRow.createCell(i - 1).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(i - 1).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(i - 1).setCellValue(stringValue + " 车架号");
		}

		// 配置
		for (Integer i : MaybachUtil.getInteger(moduleForm.getConfigure())) {
			String stringValue = getStringValue(sheetRow, i - 1);
			sheetRow.createCell(i - 1).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(i - 1).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(i - 1).setCellValue(stringValue + " 配置");
		}

		// 车辆状态
		for (Integer i : MaybachUtil.getInteger(moduleForm.getType())) {
			String stringValue = getStringValue(sheetRow, i - 1);
			sheetRow.createCell(i - 1).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(i - 1).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(i - 1).setCellValue(stringValue + " 车辆状态");
		}
		// 备注
		for (Integer i : MaybachUtil.getInteger(moduleForm.getRemark())) {
			String stringValue = getStringValue(sheetRow, i - 1);
			sheetRow.createCell(i - 1).setCellStyle(titleStyle);// 设置标题样式
			sheetRow.createCell(i - 1).setCellType(HSSFCell.CELL_TYPE_STRING);
			sheetRow.createCell(i - 1).setCellValue(stringValue + " 备注");
		}

		// 价格
		int column = Integer.parseInt(moduleForm.getPrice()) - 1;
		sheetRow.createCell(column).setCellStyle(titleStyle);// 设置标题样式
		sheetRow.createCell(column).setCellType(HSSFCell.CELL_TYPE_STRING);
		sheetRow.createCell(column).setCellValue("价格");

	}

	@Override
	public List<B2BCarRequest> excelExcelCheckByModule(ImportModuleDO importModuleDO, Row row, int rowIndex,
			int defaultType, int defaultFuel, int defaultSpec) {
		B2BCarRequest rowWrapper = new B2BCarRequest();
		rowWrapper.setByTemplate(1);
		List<String> facadeList = new ArrayList<String>();
		ModuleForm moduleForm = JSON.parseObject(importModuleDO.getModule(), ModuleForm.class);
		// 经销商ID
		Long partnerId = moduleForm.getPartnerId();
		rowWrapper.setSellerId(partnerId);
		// 车型名称
		StringBuilder sb = new StringBuilder();
		for (Integer i : MaybachUtil.getInteger(moduleForm.getModel())) {
			sb.append(getStringValue(row, i - 1));
		}
		rowWrapper.setModelStr(sb.toString());
		// 燃油
		sb = new StringBuilder();
		for (Integer i : MaybachUtil.getInteger(moduleForm.getFuel())) {
			sb.append(getStringValue(row, i - 1));
		}
		rowWrapper.setFuelStr(sb.toString());

		// 车规
		sb = new StringBuilder();
		for (Integer i : MaybachUtil.getInteger(moduleForm.getStandard())) {
			sb.append(getStringValue(row, i - 1));
		}
		rowWrapper.setSpecStr(sb.toString());

		int facadeIndex = Integer.parseInt(moduleForm.getFacade()) - 1;
		int trimIndex = Integer.parseInt(moduleForm.getTrim()) - 1;
		String stringValue = getStringValue(row, facadeIndex);
		if (facadeIndex == trimIndex) {
			if (stringValue.contains("/")) {
				String[] arr = stringValue.split("/");
				facadeList.add(arr[0]);
				if (arr.length == 2) {
					rowWrapper.setTrimStr(arr[1]);
				}
			} else {
				if (stringValue.length() > 1) {
					stringValue = stringValue.replace("色", "");
					facadeList.add(stringValue.substring(0, 1));
					String trimStr = stringValue.substring(1);
					if (StringUtil.isNotBlank(trimStr)) {
						rowWrapper.setTrimStr(trimStr);
					}
				} else {
					rowWrapper.setFacadeStr(stringValue);
				}
			}
		} else {
			facadeList.add(stringValue);
			String trimString = getStringValue(row, trimIndex);
			rowWrapper.setTrimStr(trimString);
		}

		// 车架号
		sb = new StringBuilder();
		for (Integer i : MaybachUtil.getInteger(moduleForm.getFrameNo())) {
			sb.append(getStringValue(row, i - 1).replace(".0", ""));
		}
		rowWrapper.setFrameNo(sb.toString());
		// 配置
		sb = new StringBuilder();
		for (Integer i : MaybachUtil.getInteger(moduleForm.getConfigure())) {
			sb.append(getStringValue(row, i - 1));
		}
		rowWrapper.setConfigure(sb.toString());
		// 价格
		Long price = getPrice(row, Integer.parseInt(moduleForm.getPrice()) - 1);
		rowWrapper.setPriceStr(getStringValue(row, Integer.parseInt(moduleForm.getPrice()) - 1));
		rowWrapper.setPrice(price);

		// 车辆状态
		sb = new StringBuilder();
		for (Integer i : MaybachUtil.getInteger(moduleForm.getType())) {
			sb.append(getStringValue(row, i - 1));
		}
		rowWrapper.setTypeStr(sb.toString());
		// 备注
		sb = new StringBuilder();
		for (Integer i : MaybachUtil.getInteger(moduleForm.getRemark())) {
			sb.append(getStringValue(row, i - 1)).append("。");
		}
		rowWrapper.setRemark(sb.toString());
		return b2BCarRequestsListBuilder(rowWrapper, facadeList, rowIndex, defaultFuel, defaultSpec, defaultType);
	}

	// 标题样式
	private CellStyle titleStyle = null;

	/**
	 * 样式初始化
	 */
	public void setExcelStyle(Workbook workBook) {
		// 设置列标题字体，样式
		Font titleFont = workBook.createFont();
		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 标题列样式
		titleStyle = workBook.createCellStyle();
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置边框
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setFont(titleFont);

		// 内容列样式
		CellStyle contentStyle = workBook.createCellStyle();
		contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		// contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//
		// 上下居中
	}

	/**
	 * 上传EXCEL
	 *
	 * @param wb
	 * @return
	 */
	private String updateExcel(Workbook wb) {
		String key = CalendarUtil.formatDate(new Date(), CalendarUtil.DATE_FMT_8) + "导入错误信息" + ".xlsx";
		FileOutputStream fileOutStream = null;
		try {
			File imageFile = new File("/tmp/" + key);
			fileOutStream = new FileOutputStream(imageFile);
			wb.write(fileOutStream);
		} catch (IOException e) {

		} finally {
			if (fileOutStream != null) {
				try {
					fileOutStream.close();
				} catch (IOException e) {
				}
			}
		}
		PutRet ret = IoApi.putFile(imageManager.getUpToken(), "excel/" + key, new File("/tmp/" + key), new PutExtra());
		if (org.apache.commons.lang.StringUtils.isBlank(ret.getKey())) {
			return null;
		}
		return ImageManagerImpl.IMG_PREFIX + ret.getKey();
	}

	/**
	 * 大多数列是String格式的, 已经删除 ,如果不是则返回null
	 *
	 * @param row
	 *            Excel行对象
	 * @param col
	 *            列
	 * @return
	 */
	private String getStringValue(Row row, int col) {
		Cell curCell = row.getCell(col);
		if (curCell == null) {
			return "";
		}
		return String.valueOf(curCell);
		// int colType = curCell.getCellType();
		// if (colType == Cell.CELL_TYPE_NUMERIC) {
		// double numericValue = curCell.getNumericCellValue();
		// return String.valueOf(numericValue);
		// } else if(colType == Cell.CELL_TYPE_STRING) {
		// String hd = curCell.getStringCellValue();
		// return hd.replaceAll("\n", "/").replaceAll("\r", "/").trim();
		// } else if (colType == Cell.CELL_TYPE_BLANK) {
		// return "";
		// } else {
		// return null;
		// }
	}

	/**
	 * 获取车辆
	 *
	 * @param row
	 * @param col
	 * @return
	 */
	private String getMobileNo(Row row, int col) {
		Cell curCell = row.getCell(col);
		if (curCell == null) {
			return "";
		}
		int colType = curCell.getCellType();
		if (colType == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(new Double(curCell.getNumericCellValue()).longValue());
		} else if (colType == Cell.CELL_TYPE_STRING) {
			return curCell.getStringCellValue().trim();
		} else {
			return "";
		}
	}

	/**
	 * 从万要转成元
	 *
	 * @param row
	 *            Excel行对象
	 * @param col
	 *            列
	 * @return 返回以分为单位的数字 -1表示电议
	 */
	private Long getPrice(Row row, int col) {
		Cell curCell = row.getCell(col);
		if (curCell == null) {
			return -1L;
		}
		int colType = curCell.getCellType();
		if (colType == Cell.CELL_TYPE_NUMERIC) {
			double wanYuan = curCell.getNumericCellValue();
			double cent = wanYuan * 10000 * 100;
			return new Double(cent).longValue();
		} else if (colType == Cell.CELL_TYPE_STRING) {
			String strValue = curCell.getStringCellValue().trim();
			if (strValue.matches("^\\d+(\\.?\\d*)([^0-9])*$")) {
				double wanYuan = Double.valueOf(strValue.replaceAll("[^0-9\\.]", ""));
				double cent = wanYuan * 10000 * 100;
				return new Double(cent).longValue();
			} else {
				return -1L;
			}
		} else {
			return -1L;
		}
	}

	/**
	 * 从万要转成元
	 *
	 * @param row
	 *            Excel行对象
	 * @param col
	 *            列
	 * @return 返回以分为单位的数字 -1表示电议
	 */
	private Long getChinaPrice(Row row, int col, String guidePriceStr) {
		Cell curCell = row.getCell(col);

		Long guidePrice = PriceUtil.convertPrice(guidePriceStr);
		if (guidePrice == null) {
			return -1L;
		}

		if (curCell == null) {
			return guidePrice;
		}

		int colType = curCell.getCellType();
		if (colType == Cell.CELL_TYPE_NUMERIC) {
			double wanYuan = curCell.getNumericCellValue();
			return wanYuan < 10000 ? guidePrice - new Double(wanYuan * 10000 * 100).longValue()
					: guidePrice - new Double(wanYuan * 100).longValue();

		} else if (colType == Cell.CELL_TYPE_STRING) {
			String strValue = curCell.getStringCellValue().trim();
			if (RegexUtil.match(strValue, RegexUtil.NUMBER_POINT)) {// 存数字
				return getPriceByAddAndSub(guidePrice, strValue, false);
			} else if (strValue.contains("下")) {
				int index = strValue.indexOf("下");
				if (index + 1 == strValue.length()) {
					return -1L;
				}
				StringBuilder price = new StringBuilder();
				for (index++; index < strValue.length(); index++) {
					char charAt = strValue.charAt(index);
					if ((charAt >= 48 && charAt <= 57) || charAt == '.') {
						price.append(charAt);
					} else {
						break;
					}
				}
				if (strValue.contains("点") || strValue.contains("%")) {
					return getPriceByPercent(guidePrice, price.toString(), false);
				} else {
					return getPriceByAddAndSub(guidePrice, price.toString(), false);
				}

			} else if (strValue.contains("优惠")) {
				int index = strValue.indexOf("优惠");
				if (index + 2 == strValue.length()) {
					return -1L;
				}
				StringBuilder price = new StringBuilder();
				for (index += 2; index < strValue.length(); index++) {
					char charAt = strValue.charAt(index);
					if ((charAt >= 48 && charAt <= 57) || charAt == '.') {
						price.append(charAt);
					} else {
						break;
					}
				}
				if (strValue.contains("点") || strValue.contains("%")) {
					return getPriceByPercent(guidePrice, price.toString(), false);
				} else {
					return getPriceByAddAndSub(guidePrice, price.toString(), false);
				}

			} else if (strValue.contains("加")) {
				int index = strValue.indexOf("加");
				if (index + 1 == strValue.length()) {
					return -1L;
				}
				StringBuilder price = new StringBuilder();
				for (index++; index < strValue.length(); index++) {
					char charAt = strValue.charAt(index);
					if ((charAt >= 48 && charAt <= 57) || charAt == '.') {
						price.append(charAt);
					} else {
						break;
					}
				}
				if (strValue.contains("点") || strValue.contains("%")) {
					return getPriceByPercent(guidePrice, price.toString(), true);
				} else {
					return getPriceByAddAndSub(guidePrice, price.toString(), true);
				}
			} else if (strValue.contains("折")) {
				int index = strValue.indexOf("折");
				if (index - 1 < 0) {
					return -1L;
				}
				StringBuilder price = new StringBuilder();
				for (index--; index >= 0; index--) {
					char charAt = strValue.charAt(index);
					if ((charAt >= 48 && charAt <= 57) || charAt == '.') {
						price.append(charAt);
					} else {
						break;
					}
				}
				return getPriceByPercent(guidePrice, price.reverse().toString());
			} else if (strValue.contains("w") || strValue.contains("W") || strValue.contains("万")) {
				int index = strValue.contains("w") ? strValue.indexOf("w")
						: strValue.contains("W") ? strValue.indexOf("W") : strValue.indexOf("万");
				if (index - 1 < 0) {
					return -1L;
				}
				StringBuilder price = new StringBuilder();
				for (index--; index >= 0; index--) {
					char charAt = strValue.charAt(index);
					if ((charAt >= 48 && charAt <= 57) || charAt == '.') {
						price.append(charAt);
					} else {
						break;
					}
				}
				return getPriceByAddAndSub(guidePrice, price.reverse().toString(), false);
			}
		}

		return -1L;
	}

	private Long getPriceByPercent(Long guidePrice, String strValue) {
		if (StringUtils.isBlank(strValue)) {
			return -1L;
		}
		Double guiPrice = Double.parseDouble(strValue);
		return new BigDecimal(guidePrice * (guiPrice / 10)).longValue();

	}

	private Long getPriceByPercent(Long guidePrice, String strValue, boolean add) {
		if (StringUtils.isBlank(strValue)) {
			return -1L;
		}
		Double guiPrice = Double.parseDouble(strValue);
		return add ? new BigDecimal(guidePrice * ((100 + guiPrice) / 100)).longValue()
				: new BigDecimal(guidePrice * ((100 - guiPrice) / 100)).longValue();
	}

	private Long getPriceByAddAndSub(Long guidePrice, String strValue, boolean add) {
		if (StringUtils.isBlank(strValue)) {
			return -1L;
		}
		Double guiPrice = Double.parseDouble(strValue);
		if (add) {
			return guiPrice < 1000 ? guidePrice + PriceUtil.convertPrice(strValue)
					: guidePrice + PriceUtil.convertPriceYuan(strValue);
		} else {
			return guiPrice < 1000 ? guidePrice - PriceUtil.convertPrice(strValue)
					: guidePrice - PriceUtil.convertPriceYuan(strValue);
		}
	}

	/**
	 * 根据电话获取分销商
	 *
	 * @param mobile
	 *            电话
	 * @return 分销商ID
	 */
	private Long getSellerId(String mobile) {
		try {

			if (!RegexUtil.match(mobile, RegexUtil.PHONE_REGEX)) {
				UserDO user = userManager.queryByExtNo(mobile);
				if (user != null) {
					return user.getPartnerId();
				}
			}

			PartnerQuery partnerQuery = new PartnerQuery();
			partnerQuery.createCriteria().andContactsPhoneEqualTo(mobile);
			partnerQuery.createCriteria().andStatusEqualTo(PartnerStatusEnum.normal.getCode());
			List<PartnerDO> partnerDOs = partnerManager.selectByQuery(partnerQuery);
			if (CollectionUtils.isNotEmpty(partnerDOs) && partnerDOs.size() == 1 && partnerDOs.get(0) != null) {
				return partnerDOs.get(0).getId();
			}

			UserQuery userQuery = new UserQuery();
			userQuery.createCriteria().andLoginNameEqualTo(mobile);
			userQuery.createCriteria().andPartnerIdIsNotNull();
			List<UserDO> userList = userManager.selectByQuery(userQuery);
			if (CollectionUtils.isNotEmpty(userList) && userList.size() == 1 && userList.get(0) != null) {
				return userList.get(0).getPartnerId();
			}

			return -1L;
		} catch (Exception e) {
			log.error("import error mobile is " + mobile, e);
			return -1L;
		}
	}

	/**
	 * 错误信息记录
	 *
	 * @param sheetName
	 *            工作表的名称
	 * @param rowIndex
	 *            行数 +1
	 * @param columnIndex
	 *            列数
	 * @param errorList
	 *            错误信息集合
	 * @param columnNameList
	 */
	private void logCellFormatError(String sheetName, int rowIndex, int columnIndex, Set<String> errorList,
			List<String> columnNameList) {
		rowIndex++;
		String s = columnNameList.get(columnIndex);
		errorList.add(sheetName + "工作表,第" + rowIndex + "行," + s + "列格式有误;");
	}

	/**
	 * 构造方法
	 *
	 * @param rowWrapper
	 *            需要复制的对象
	 * @param facadeList
	 *            外观list
	 * @param defaultFuel
	 * @param defaultSpec
	 * @param defaultType
	 * @return 返回插入对象
	 */
	private List<B2BCarRequest> b2BCarRequestsListBuilder(B2BCarRequest rowWrapper, List<String> facadeList,
			int rowIndex, int defaultFuel, int defaultSpec, int defaultType) {

		List<B2BCarRequest> list = new ArrayList<B2BCarRequest>();
		if (!CollectionUtils.isEmpty(facadeList)) {
			for (String facade : facadeList) {
				B2BCarRequest b2BCarRequest = copyCarRequest(rowWrapper, rowIndex, defaultFuel, defaultSpec,
						defaultType);
				b2BCarRequest.setFacadeStr(facade);
				list.add(b2BCarRequest);
			}
		} else {
			B2BCarRequest b2BCarRequest = copyCarRequest(rowWrapper, rowIndex, defaultFuel, defaultSpec, defaultType);
			b2BCarRequest.setFacadeStr(rowWrapper.getFacadeStr());
			list.add(b2BCarRequest);
		}

		return list;
	}

	private static B2BCarRequest copyCarRequest(B2BCarRequest rowWrapper, int rowIndex, int defaultFuel,
			int defaultSpec, int defaultType) {
		B2BCarRequest b2BCarRequest = new B2BCarRequest();

		b2BCarRequest.setDefaultFuel(CarFuelEnum.get(defaultFuel).getName());
		b2BCarRequest.setDefaultSpec(CarStandardEnum.valueOf(defaultSpec).getDesc());
		b2BCarRequest.setDefaultType(CarTypeEnum.get(defaultType).getName());

		b2BCarRequest.setGuidePrice(rowWrapper.getGuidePrice());
		b2BCarRequest.setPriceStr(rowWrapper.getPriceStr());

		b2BCarRequest.setSeriesNo((long) rowIndex);
		b2BCarRequest.setSeriesStr(rowWrapper.getSeriesStr());
		b2BCarRequest.setSellerId(rowWrapper.getSellerId());
		b2BCarRequest.setModelStr(rowWrapper.getModelStr());
		b2BCarRequest.setFuelStr(rowWrapper.getFuelStr());
		b2BCarRequest.setSpecStr(rowWrapper.getSpecStr());
		// 默认电议
		b2BCarRequest.setPrice(rowWrapper.getPrice() != null ? rowWrapper.getPrice() : -1L);
		b2BCarRequest.setTrimStr(rowWrapper.getTrimStr());
		b2BCarRequest.setFrameNo(rowWrapper.getFrameNo());
		b2BCarRequest.setConfigure(rowWrapper.getConfigure());
		b2BCarRequest.setTypeStr(rowWrapper.getTypeStr());
		b2BCarRequest.setRemark(rowWrapper.getRemark());
		b2BCarRequest.setFacadeStr(rowWrapper.getFacadeStr());
		b2BCarRequest.setQuantity(1);// 默认一台
		b2BCarRequest.setByTemplate(rowWrapper.getByTemplate());
		return b2BCarRequest;
	}
}